Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

RUN STORED-PROCEDURE statement

The RUN STORED-PROC statement runs an RDBMS stored procedure or allows you to send PL-SQL to an ORACLE Server based data source using an OpenEdge DataServer. It contains a procedure-name which is either the:

Name of the RDBMS stored procedure that you want to run.
Progress built-in procedure name, send–sql–statement, to send PL/SQL to an ORACLE Server based data source.

LOAD-RESULT-INTO phrase

The LOAD-RESULT-INTO function loads the result set into a temp-table only where <handle> is a variable of type handle. Note that handle can also be defined as extent to enable you to pass more than one temp-table handle in those instances where the SQL statement is defined to return more than one result set.

When used with the send-sql-statement or stored procedure to load a result set into a Progress temp-table, this function carries an implicit CLOSE-STORED PROCEDURE statement. However, the use of this function is optional so existing applications will not be affected.

Note: The Progress client issues an error at runtime if the variable of type handle with the LOAD-RESULT-INTO function does not point to a temp-table.

When used with the LOAD-RESULT-INTO phrase, the temp-table handle identifies the temp-table to which the result set will be loaded.

You can specify an array of one or more temp-table handle elements to retrieve stored procedure result sets and have the DataServer load the result set data directly into the associated temp-table(s). This approach allows you to have direct access to the fields defined in the temp-table.

The following types of temp-tables can support result sets:

Static — A temp-table whose schema is defined at compile time.
Dynamic — A temp-table whose schema is defined at run time. There are two types of dynamic temp-tables: dynamic-prepared and dynamic-unprepared.

For additional details about using the LOAD-RESULT-INTO phrase with the temp-table handle, see the "Loading a result set into a temp-table" section.

PROC-STATUS phrase

The PROC-STATUS phase returns the return status from an ORACLE Server stored procedure. The return status is an integer value that indicates whether a stored procedure succeeded or failed; if it failed, a code indicating why it failed. See your ORACLE DataServer documentation for descriptions of the possible values for the return status.

PROC-HANDLE phrase

The PROC-HANDLE phrase allows you to specify a handle to act as a unique identifier for an ORACLE DataServer stored procedure. For example, the PROC-HANDLE assigns a value to the specified integer field or variable (integer–field) that uniquely identifies the:

Stored procedure that is returning results from ORACLE.
SQL cursor that is used to retrieve results from an ORACLE Server data source.

Note these additional points about the PROC-HANDLE:

Progress Software Corporation recommends that you specify a procedure handle for each stored procedure that you run.
You do not have to specify a handle if there is only one active stored procedure and you do not include SQL statements in the OpenEdge application.

NO-ERROR option

The NO-ERROR option specifies that any ERROR condition that the RUN STORED–PROCEDURE statement produces is suppressed. Before you close a stored procedure, check the ERROR–STATUS handle for information on any errors that occurred. You receive an error when you attempt to close a stored procedure that did not start.

Note: This option must appear before any runtime parameter list.

PARAM phrase

The Param phrase identifies a run-time parameter to be passed to the stored procedure. A parameter has the following syntax:

Syntax

[([INPUT|OUTPUT|INPUT OUTPUT] [PARAM parameter-name =] expression, ...      [INPUT|OUTPUT|INPUT OUTPUT] [PARAM parameter-name =] expression )]

An expression is a constant, field name, variable name, or expression. INPUT is the default. OUTPUT and INPUT–OUTPUT parameters must be record fields or program variables.

If you run send–sql–statement for an ORACLE-based data source, you must pass a single character expression parameter containing the SQL statement you want the data source to execute.

Note: The ORACLE DataServer only supports one SQL statement with the send-sql-statement option.

If you do not specify parameter–name (the name of a keyword parameter defined by the stored procedure), you must supply all of the parameters in correct order. If you do specify parameter–name, you must precede your assignment statement with the keyword PARAM. If you do not supply a required parameter, and no default is specified in the stored procedure, you receive a run-time error.